import pymysql


class UserRegister:

    def ConnectSQL(self):
        # 连接数据库
        db = pymysql.connect(host="127.0.0.1",
                             # host='116.63.136.129',
                             user='',  # edit
                             password='',  # edit
                             charset='utf8')
        cursor = db.cursor()  # 游标对象
        Create_sql = "CREATE DATABASE IF NOT EXISTS GameUser;"

        Connect_sql = "use GameUser;"  # 用户数据库为GameUser
        Create_Table_sql = "create table users(user_id char(9),password char(6),score char(2))character set utf8;"
        try:
            cursor.execute(Create_sql)
            cursor.execute(Connect_sql)  # 执行sql语句
            # cursor.execute(Create_Table_sql)
            return db  # 成功连接数据库则返回连接对象
        except Exception as e:
            return None  # 连接数据库失败
        # db.close()

    def Loginin(self, db, account, password):
        # 用户登录
        cursor = db.cursor()  # 游标对象
        sql1 = "select * from users where user_id=%s;"
        sql2 = "select * from users where user_id=%s AND password=%s;"
        try:
            cursor.execute(sql1 % account)
            results = cursor.fetchall()
            if results:
                cursor.execute(sql2, [account, password])
                results2 = cursor.fetchall()
                if results2:
                    # 查询成功，表示该人已经注册过，直接登录即可
                    return "Successfully Login!"
                else:
                    return "Wrong Password!"
            else:
                # 该账号就不存在，需要注册
                return "Not Exits, Need to Register!"
        except Exception as e:
            raise e
            return "Error!"
        db.close()

    def Register(self, db, account, password):
        # 用户注册
        cursor = db.cursor()  # 游标对象
        # 先检查该账号是否已存在
        sql_check = "select * from users where user_id=%s;"
        sql_insert = """insert into users(user_id,password,score) values (%s , %s , '0')"""
        try:
            cursor.execute(sql_check % account)
            result = cursor.fetchall()
            if result:
                # 该账号存在，则不用注册直接登录即可
                return False
            else:
                # 该账号不存在，则可以注册
                cursor.execute(sql_insert, [account, password])
                db.commit()
                return True
        except Exception as e:
            db.rollback()
            return False
        db.close()
